package com.dao;

import com.bean.Student;
import com.utils.DBHelper;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    /**
     * 添加方法
     * @param no
     * @param name
     * @param age
     * @param sex
     * @param score
     * @param time
     * @throws Exception
     */
    public void insert(String no, String name, int age, String sex, float score, Date time) throws Exception {
        String sql = "insert into student_info (stu_no, stu_name, stu_age, stu_sex, stu_score, stu_time) values (?, ?, ?, ?, ?, ?)";
        System.out.println("要执行的语句是：" + sql);

        try (Connection connection = DBHelper.getConnection();
            PreparedStatement statement = connection.prepareStatement(sql)){
            statement.setString(1, no);
            statement.setString(2, name);
            statement.setInt(3, age);
            statement.setString(4, sex);
            statement.setFloat(5, score);
            statement.setDate(6, time);
            statement.executeUpdate();
        }

    }

    /**
     * 查询所有信息方法
     *
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public List<Student> list() throws SQLException, ClassNotFoundException {
        List<Student> students = new ArrayList<>();
        String sql = "select * from student_info";
        System.out.println("要执行的语句是：" + sql);

        try (Connection connection = DBHelper.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql);
             ResultSet resultSet = statement.executeQuery()) {
            while (resultSet.next()) {
                students.add(
                     new Student(
                         resultSet.getString(1),
                         resultSet.getString(2),
                         resultSet.getInt(3),
                         resultSet.getString(4),
                         resultSet.getFloat(5),
                         resultSet.getDate(6)
                    )
                );
            }
        }
        return students;
    }

    /**
     * 模糊查询方法
     * 这里查询条件是有两个参数的
     *
     * @param condition
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public List<Student> queryByLike(String condition) throws SQLException, ClassNotFoundException {
        List<Student> students = new ArrayList<>();
        String sql = "select * from student_info where stu_name like ? or stu_sex like ?";
        System.out.println("要执行的语句是：" + sql);

        try (Connection connection = DBHelper.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, "%" + condition + "%");
            statement.setString(2, "%" + condition + "%");

            try (ResultSet resultSet = statement.executeQuery()) {
                while (resultSet.next()) {
                    students.add(
                        new Student(
                             resultSet.getString(1),
                             resultSet.getString(2),
                             resultSet.getInt(3),
                             resultSet.getString(4),
                             resultSet.getFloat(5),
                             resultSet.getDate(6)
                        )
                    );
                }
            }
            return students;
        }
    }

    /**
     * 删除方法
     *
     * @param no
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public int del(String no) throws SQLException, ClassNotFoundException {
        String sql = "delete from student_info where stu_no = ?";
        System.out.println("要执行的语句是：" + sql);

        try (Connection connection = DBHelper.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, no);
            return statement.executeUpdate();
        }
    }

    /**
     * 修改方法之查询单条信息
     *
     * @param no
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public Student get(String no) throws SQLException, ClassNotFoundException {
        String sql = "select stu_name, stu_age, stu_sex, stu_score, stu_time from student_info where stu_no = ?";
        System.out.println("要执行的语句是：" + sql);

        try (Connection connection = DBHelper.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, no);
            try (ResultSet resultSet = statement.executeQuery()) {
                if (resultSet.next()) {
                    String name = resultSet.getString(1);
                    int age = resultSet.getInt(2);
                    String sex = resultSet.getString(3);
                    float score = resultSet.getFloat(4);
                    Date time = resultSet.getDate(5);

                    Student student = new Student();
                    student.setNo(no);
                    student.setName(name);
                    student.setAge(age);
                    student.setSex(sex);
                    student.setScore(score);
                    student.setTime(time);
                    return student;
                }
                return null;
            }
        }
    }

    /**
     * 修改方法
     *
     * @param no
     * @param name
     * @param age
     * @param sex
     * @param score
     * @param time
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void update(String no, String name, int age, String sex, float score, Date time) throws SQLException, ClassNotFoundException {
        String sql = "update student_info set stu_name = ?, stu_age = ?, stu_sex = ?, stu_score = ?, stu_time = ? where stu_no = ?";
        System.out.println("要执行的语句是：" + sql);

        try (Connection connection = DBHelper.getConnection();
             PreparedStatement statement = connection.prepareStatement(sql)) {
            statement.setString(1, name);
            statement.setInt(2, age);
            statement.setString(3, sex);
            statement.setFloat(4, score);
            statement.setDate(5, time);
            statement.setString(6, no);
            statement.executeUpdate();
        }
    }

    // 测试方法
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
//        System.out.println(new StudentDao().queryByLike(String.valueOf(1)));
        System.out.println(new StudentDao().get("1"));
    }
}
